"""
Case Type   : 提示信息函数RAISE_APPLICATION_ERROR的使用
Case Name   : 异常处理与gs_dump/gs_restore测试,部分功能合理报错
Create At   : 2025.3.27
Owner       : xuwenfei1
Description :
    1、创建存储过程
    2、嵌套子程序包含异常处理
    3、备份恢复
    4、调用函数
    5、清理环境
Expect      :
    1、成功
    2、成功
    3、备份和恢复成功
    4、调用成功
    5、成功
History     :
    modified by @xuwenfei1 2025-3.19:在执行show grants for user前，将参数b_compatibility_user_host_auth的值设置为true
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class Grammar(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:初始化----')
        self.pri_sh = CommonSH('PrimaryDbAmode')
        self.user_node = Node('PrimaryDbAmode')
        self.constant = Constant()
        self.db_func1 = 'func1_15'
        self.db_func2 = 'func2_15'
        self.db_proc = 'proc1_15'
        self.db_1 = 'db_1_15'
        self.db_2 = 'db_2_15'
        self.db_3 = 'db_3_15'
        self.db_tb1 = 'tb1_15'

    def test_main(self):
        text = '----step0:前置条件;expect:成功----'
        self.log.info(text)
        sql_cmd = f'''
            drop database if exists {self.db_1};
            drop database if exists {self.db_2};
            drop database if exists {self.db_3};
            create database {self.db_1};
            create database {self.db_2};
            create database {self.db_3};
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertEqual(sql_res.count('DROP DATABASE'), 3, '执行失败' + text)
        self.assertEqual(sql_res.count('CREATE DATABASE'), 3, '执行失败' + text)

        text = '----step1: 创建存储过程;expect: 成功----'
        self.log.info(text)
        sql_cmd = f'''
            CREATE OR REPLACE PROCEDURE {self.db_proc}(col1 int,col2 int)
            IS 
            BEGIN 
                IF col1 - col2 <=-10 THEN
                    RAISE_APPLICATION_ERROR(-29999+9000, '+',true);
                ELSIF col1 - col2 < 0 THEN
                    RAISE_APPLICATION_ERROR(-10000-10100, '-',false);
                ELSIF col1 - col2 = 0 THEN
                    RAISE_APPLICATION_ERROR(-2611*8, '*');
                ELSE
                    RAISE_APPLICATION_ERROR(-100000/5-15, '/');
                END IF;
            END; 
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_1)
        self.log.info(sql_res)
        self.assertIn('CREATE PROCEDURE', sql_res, '执行失败' + text)
        sql_cmd = f'''
            CREATE OR REPLACE function {self.db_func1} (v_personid INT) return int
            IS
                v_name VARCHAR(50):='aaa';
                v_email VARCHAR(50):='bbbb';
                e1 EXCEPTION;
                PRAGMA EXCEPTION_INIT (e1, -20001);
            BEGIN
                IF v_personid>1 THEN
                    RAISE_APPLICATION_ERROR(-20001, '+',true);
                ELSE
                    raise info '%', v_name || v_email;
                    return 0;
                END IF;
                EXCEPTION WHEN E1 THEN
                    raise info '%', v_name ||' '||SQLCODE||' '||SQLERRM;
                    return SQLCODE;
            END;
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_1)
        self.log.info(sql_res)
        self.assertIn('CREATE FUNCTION', sql_res, '执行失败' + text)
        sql_cmd = f'''
                    create table {self.db_tb1}(id int,col1 varchar(30),col2 varchar(30),col3 varchar(30));
                '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_1)
        self.log.info(sql_res)
        self.assertIn('CREATE TABLE', sql_res, '执行失败' + text)

        text = '----step2: 嵌套子程序包含异常处理;expect: 成功----'
        self.log.info(text)
        sql_cmd = f'''
                    create or replace function {self.db_func2}(col1 int,col2 int,col3 int,col4 int)return int AS
                    DECLARE
                    procedure proc1_13(col1 int)
                        is
                        var1 int;
                        function proc1_13_1(col1 int,col2 int,col3 int,col4 int) return int
                            is
                            begin
                                if col1=1 THEN
                                    RAISE_APPLICATION_ERROR(-20808, 'col1=1');
                                end if;
                                insert into {self.db_tb1} values(col1,col2,col3,col4);
                                select count(*) from {self.db_tb1} into var1;
                                return var1+10;
                                EXCEPTION WHEN OTHERS THEN
                                    raise info '% --- %',SQLCODE,SQLERRM;
                                    return SQLCODE;
                            end;
                            function proc1_13_2 return int
                            is
                            begin
                                insert into {self.db_tb1} values(99,99,99,99);
                                select count(*) from {self.db_tb1} into var1;
                                if var1>15 THEN
                                    RAISE_APPLICATION_ERROR(-20810, 'count>15');
                                end if;
                                return var1+10;
                                EXCEPTION WHEN OTHERS THEN
                                    raise info '% --- %',SQLCODE,SQLERRM;
                                    return SQLCODE;
                            end;
                            begin
                                select proc1_13_2() into var1;
                                select proc1_13_1(col1,9,9,var1) into var1;
                                commit;
                                raise info 'proc1_13 %',var1;
                                select count(*) from {self.db_tb1} into var1;
                                if var1>10 THEN
                                    RAISE_APPLICATION_ERROR(-20815, 'col1>10');
                                end if;
                                EXCEPTION WHEN OTHERS THEN
                                    raise info '% --- %',SQLCODE,SQLERRM;
                            end;
                    procedure proc2_13(col1 out int)
                        is
                        var1 int;
                        function proc2_13_1(a1 int,a2 int,a3 int,a4 int,b1 out int) return int2
                        is
                        begin
                            insert into {self.db_tb1} values(a1,a2,a3,a4);
                            select count(*) from {self.db_tb1} into var1;
                            b1=var1;
                            return var1;
                            end;
                            function proc2_13_1(b1 out int) return int2
                            is
                            begin
                                insert into {self.db_tb1} values(999,999,999,999);
                                select count(*) from {self.db_tb1} into var1;
                                b1=var1;
                                return var1;
                            end;
                        begin
                            select proc2_13_1() into var1;
                            select proc2_13_1(199,199,199,var1) into var1;
                            col1:=var1;
                            raise info 'proc2_13 %',var1;
                            select count(*) from {self.db_tb1} into var1;
                            commit;
                            if var1>7 THEN
                                RAISE_APPLICATION_ERROR(-20825, 'count>7');
                            end if;
                            EXCEPTION WHEN OTHERS THEN
                                raise info '% --- %',SQLCODE,SQLERRM;
                        end;
                        
                        var1 int;
                        BEGIN
                            proc1_13(col1);
                            commit;
                            select proc2_13() into var1;
                            raise info '%',var1;
                            return var1;
                        END;
                '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_1)
        self.log.info(sql_res)
        self.assertIn('CREATE FUNCTION', sql_res, '执行失败' + text)

        text = '----step3: 备份恢复;expect: 备份成功，恢复成功----'
        self.log.info(text)
        excute_cmd = f'''source {macro.DB_ENV_PATH} ;
                gs_dump {self.db_1} -F p -f backup1_15.sql;
                gs_dump {self.db_1} -F c -f backup2_15.dmp;
                gsql -d {self.db_2} -f backup1_15.sql;
                gs_restore -d {self.db_3} backup2_15.dmp;
        '''
        self.log.info(excute_cmd)
        sql_res = self.user_node.sh(excute_cmd).result()
        self.log.info(sql_res)
        self.assertIn(f'dump database {self.db_1} successfully', sql_res, '执行失败' + text)
        self.assertIn(f'dump database {self.db_1} successfully', sql_res, '执行失败' + text)
        self.assertIn('CREATE PROCEDURE', sql_res, '执行失败' + text)
        self.assertIn('restore operation successful', sql_res, '执行失败' + text)

        text = '----step4: 创建存储过程;expect: 成功----'
        self.log.info(text)
        sql_cmd = f'''
                    call {self.db_proc}(-5,110);
                    call {self.db_proc}(1,10);
                    call {self.db_proc}(3,3);
                    call {self.db_proc}(23,3);
                    call {self.db_func1}(0);
                    call {self.db_func1}(10);
                '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_2)
        self.log.info(sql_res)
        self.assertIn('ERROR:  +', sql_res, '执行失败' + text)
        self.assertIn('ERROR:  -', sql_res, '执行失败' + text)
        self.assertIn('ERROR:  *', sql_res, '执行失败' + text)
        self.assertIn('ERROR:  /', sql_res, '执行失败' + text)
        self.assertIn('INFO:  aaabbbb', sql_res, '执行失败' + text)
        self.assertIn('INFO:  aaa -20001 +', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(1,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_2)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n        3\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(2,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_2)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n        7\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(3,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_2)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n       11\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(4,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_2)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n       15\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(5,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_2)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n       18\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(6,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_2)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n       21\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(7,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_2)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n       24\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'select * from {self.db_tb1} order by 1,2,3,4;'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_2)
        self.log.info(sql_res)
        self.assertIn('24 rows', sql_res, '执行失败' + text)

        sql_cmd = f'''
                    call {self.db_proc}(-5,110);
                    call {self.db_proc}(1,10);
                    call {self.db_proc}(3,3);
                    call {self.db_proc}(23,3);
                    call {self.db_func1}(0);
                    call {self.db_func1}(10);
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_3)
        self.log.info(sql_res)
        self.assertIn('ERROR:  +', sql_res, '执行失败' + text)
        self.assertIn('ERROR:  -', sql_res, '执行失败' + text)
        self.assertIn('ERROR:  *', sql_res, '执行失败' + text)
        self.assertIn('ERROR:  /', sql_res, '执行失败' + text)
        self.assertIn('INFO:  aaabbbb', sql_res, '执行失败' + text)
        self.assertIn('INFO:  aaa -20001 +', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(1,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_3)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n        3\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(2,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_3)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n        7\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(3,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_3)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n       11\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(4,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_3)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n       15\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(5,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_3)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n       18\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(6,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_3)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n       21\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'call {self.db_func2}(7,2,3,4);'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_3)
        self.log.info(sql_res)
        self.assertIn('func2_15 \n----------\n       24\n(1 row)', sql_res, '执行失败' + text)
        sql_cmd = f'select * from {self.db_tb1} order by 1,2,3,4;'
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_3)
        self.log.info(sql_res)
        self.assertIn('24 rows', sql_res, '执行失败' + text)

    def tearDown(self):
        text = '----step:清理数据; expect:清理成功----'
        self.log.info(text)
        drop_sql = f'''
            drop database if exists {self.db_1};
            drop database if exists {self.db_2};
            drop database if exists {self.db_3};'''
        sql_res = self.pri_sh.execut_db_sql(drop_sql, dbname='postgres')
        self.log.info(sql_res)
        self.assertEqual(sql_res.count('DROP DATABASE'), 3, '执行失败' + text)

        self.log.info(f'----{os.path.basename(__file__)}:执行完毕----')
